Read cell address and pass to a Range

Ask Time：2020-02-24T01:28:25 Author：Lucas Magalhães

Can anyone help me?

I'm trying to create a range based on 2 vars "cel1" and "cel2" and copy that range to another sheet.

This is the code:

function EXEMPLO() {
  var sheet1 = SpreadsheetApp.openById('1dpp7Ybge8LRb61axkiOolVFFPE6YuVuslOppxz-CU1g').getSheetByName('raw_data');
  var sheet2 = SpreadsheetApp.openById('1dpp7Ybge8LRb61axkiOolVFFPE6YuVuslOppxz-CU1g').getSheetByName('Dados');
  sheet1.getRange('A1').activate();
  do {
    sheet1.getActiveCell().offset(1,0).activate();
  }while (sheet1.getActiveCell().getValue() != 'Data');
  sheet1.getActiveCell().offset(1,0).activate();
  var cel1 = sheet1.getActiveRange(); \\ reading cell 1 address
  sheet1.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  sheet1.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.NEXT).activate();
  var cel2 = sheet1.getActiveRange(); \\ reading cell2 address
  sheet2.getRange('A1').activate(); 
  sheet2.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  sheet2.getActiveCell().offset(1,0).activate();
  sheet1.getRange(cel1+":"+cel2).copyTo(sheet2.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); \\ try to copy the range to another sheet
};

Author:Lucas Magalhães，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/60364897/read-cell-address-and-pass-to-a-range

Rubén - People First :

To read a value from a single cell use getValue();\n\nIt's worthy to note that some times you could get better performance results by reading all the cells values that you need by taking a larger range that encompasses all the cells, then use getValues() to to get all the values in one pass and finally using link values[i][k] to get a specific cell value that you need because the Spreadsheet Service methods like getValue() are \"expensive\" (they take a lot of time compared with the use of values[i][k])\n\nAlso it's worthy to note, that the Google Sheets macro recorder introduces a lot of activate() that could be stripped out if you learn how to get the ranges that you need by using the range reference, specially if does references are absolute (doesn't really depend on the active cell)",

2020-02-23T21:55:02

Read cell address and pass to a Range